﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;

namespace ProjectTools
{
    enum ExecType
    {
        Exec,
        Open
    }

    public class SqlHelper
    {
        private string sqlConnectionString;

        public string SqlConnectionString
        {
            private get { return sqlConnectionString; }
            set { sqlConnectionString = value; }
        }

        public SqlHelper(string connectionString)
        {
            this.sqlConnectionString = connectionString;
        }

        #region 打开存储过程，并且获取数据集
        /// <summary>
        /// 打开存储过程，并且获取数据集
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="Params">参数字典</param>
        public DataSet OpenProcedure(String procedureName, Dictionary<String, Object> Params)
        {
            try
            {
                DataSet ds;
                using (SqlConnection sqlConn = new SqlConnection(this.sqlConnectionString))
                {
                    sqlConn.Open();
                    try
                    {
                        ds = ExecProce(ExecType.Open, sqlConn, procedureName, ref Params);
                    }
                    finally
                    {
                        sqlConn.Close();
                        sqlConn.Dispose();
                    }
                }
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }

        }
        /// <summary>
        /// 打开存储过程，并且获取数据集
        /// </summary>
        /// <param name="procedureName"></param>
        /// <param name="Params">参数字典</param>
        public DataSet OpenProcedure(String procedureName, ref Dictionary<String, Object> Params)
        {
            try
            {
                DataSet ds;
                using (SqlConnection sqlConn = new SqlConnection(this.sqlConnectionString))
                {
                    sqlConn.Open();
                    try
                    {
                        ds = ExecProce(ExecType.Open, sqlConn, procedureName, ref Params);
                    }
                    finally
                    {
                        sqlConn.Close();
                        sqlConn.Dispose();
                    }
                }
                return ds;
            }
            catch (Exception e)
            {
                throw e;
            }

        }
        #endregion

        #region 执行存储过程
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="Params">参数字典</param>
        public void ExecProcedure(String procedureName, Dictionary<String, Object> Params)
        {
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(this.sqlConnectionString))
                {
                    sqlConn.Open();
                    try
                    {
                        ExecProce(ExecType.Exec, sqlConn, procedureName, ref Params);
                    }
                    finally
                    {
                        sqlConn.Close();
                        sqlConn.Dispose();
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }

        }
        /// <summary>
        /// 执行存储过程
        /// </summary>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="Params">参数字典</param>
        public void ExecProcedure(String procedureName, ref Dictionary<String, Object> Params)
        {
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(this.sqlConnectionString))
                {
                    sqlConn.Open();
                    try
                    {
                        ExecProce(ExecType.Exec, sqlConn, procedureName, ref Params);
                    }
                    finally
                    {
                        sqlConn.Close();
                        sqlConn.Dispose();
                    }
                }
            }
            catch (Exception e)
            {
                throw e;
            }

        }
        #endregion


        /// <summary>
        /// 将sql中的数据类型转换为SqlDBType类型
        /// </summary>
        /// <param name="SqlDateType">传入的Sql中的数据类型</param>
        /// <returns></returns>
        private static SqlDbType SqlDateTypeToDBType(String SqlDateType)
        {
            SqlDbType dbType = SqlDbType.Variant;//默认为Object

            switch (SqlDateType)
            {
                case "int":
                    dbType = SqlDbType.Int;
                    break;
                case "varchar":
                    dbType = SqlDbType.VarChar;
                    break;
                case "bit":
                    dbType = SqlDbType.Bit;
                    break;
                case "datetime":
                    dbType = SqlDbType.DateTime;
                    break;
                case "decimal":
                    dbType = SqlDbType.Decimal;
                    break;
                case "float":
                    dbType = SqlDbType.Float;
                    break;
                case "image":
                    dbType = SqlDbType.Image;
                    break;
                case "money":
                    dbType = SqlDbType.Money;
                    break;
                case "ntext":
                    dbType = SqlDbType.NText;
                    break;
                case "nvarchar":
                    dbType = SqlDbType.NVarChar;
                    break;
                case "smalldatetime":
                    dbType = SqlDbType.SmallDateTime;
                    break;
                case "smallint":
                    dbType = SqlDbType.SmallInt;
                    break;
                case "text":
                    dbType = SqlDbType.Text;
                    break;
                case "bigint":
                    dbType = SqlDbType.BigInt;
                    break;
                case "binary":
                    dbType = SqlDbType.Binary;
                    break;
                case "char":
                    dbType = SqlDbType.Char;
                    break;
                case "nchar":
                    dbType = SqlDbType.NChar;
                    break;
                case "numeric":
                    dbType = SqlDbType.Decimal;
                    break;
                case "real":
                    dbType = SqlDbType.Real;
                    break;
                case "smallmoney":
                    dbType = SqlDbType.SmallMoney;
                    break;
                case "sql_variant":
                    dbType = SqlDbType.Variant;
                    break;
                case "timestamp":
                    dbType = SqlDbType.Timestamp;
                    break;
                case "tinyint":
                    dbType = SqlDbType.TinyInt;
                    break;
                case "uniqueidentifier":
                    dbType = SqlDbType.UniqueIdentifier;
                    break;
                case "varbinary":
                    dbType = SqlDbType.VarBinary;
                    break;
                case "xml":
                    dbType = SqlDbType.Xml;
                    break;
            }
            return dbType;
        }

        #region 根据存储过程名获取存储过程中各参数的类型，名称，传递方向
        /// <summary>
        /// 根据存储过程名获取存储过程中各参数的类型，名称，传递方向
        /// </summary>
        /// <param name="ProceName"></param>
        private SqlParameter[] GetParamFromProceName(String ProceName)
        {
            try
            {
                using (SqlConnection sqlConn = new SqlConnection(this.sqlConnectionString))
                {
                    sqlConn.Open();
                    using (SqlCommand SqlCmd = new SqlCommand())
                    {
                        SqlCmd.Connection = sqlConn;

                        SqlCmd.CommandText = "GetProcedureParam";

                        SqlCmd.CommandType = CommandType.StoredProcedure;

                        SqlCmd.Parameters.Add("@ProcedureName", SqlDbType.NVarChar).Value = ProceName.Trim();

                        DataSet DS = new DataSet();
                        SqlDataAdapter SDA = new SqlDataAdapter(SqlCmd);
                        SDA.Fill(DS);

                        SqlParameter[] paras = new SqlParameter[DS.Tables[0].Rows.Count];
                        int i = 0;
                        foreach (DataRow DR in DS.Tables[0].Rows)
                        {
                            paras[i] = new SqlParameter();
                            paras[i].ParameterName = DR["name"].ToString();
                            paras[i].SqlDbType = SqlDateTypeToDBType(DR["type"].ToString());
                            if (Convert.ToInt32(DR["isoutparam"]) == 1)
                                paras[i].Direction = ParameterDirection.InputOutput;
                            else
                                paras[i].Direction = ParameterDirection.Input;

                            paras[i].Size = Convert.ToInt32(DR["length"]);
                            i++;
                        }
                        sqlConn.Close();
                        return paras;

                    }

                }
            }
            catch (Exception e)
            {
                throw new Exception("<GetParamFromProceName><" + ProceName + "> " + e.Message);
            }
        }
        #endregion

        #region
        /// <summary>
        /// 询存储过程，并返回Dataset
        /// </summary>
        /// <param name="execType">执行还是打开</param>
        /// <param name="sqlConn">连接<</param>
        /// <param name="procedureName">存储过程名</param>
        /// <param name="Params">参数字典</param>
        /// <returns></returns>
        private DataSet ExecProce(ExecType execType, SqlConnection sqlConn, String procedureName, ref Dictionary<String, Object> Params)
        {
            try
            {
                using (SqlCommand SqlCmd = new SqlCommand())
                {
                    SqlParameter[] ProceParams = GetParamFromProceName(procedureName); //获取各参数信息       
                    SqlCmd.Connection = sqlConn;
                    SqlCmd.CommandText = procedureName;
                    SqlCmd.CommandType = CommandType.StoredProcedure;

                    foreach (SqlParameter ProceParam in ProceParams)
                    {
                        try
                        {
                            if (ProceParam.SqlDbType == SqlDbType.NVarChar || ProceParam.SqlDbType == SqlDbType.VarChar)
                                SqlCmd.Parameters.Add(ProceParam.ParameterName, ProceParam.SqlDbType, ProceParam.Size).Direction = ProceParam.Direction;
                            else
                                SqlCmd.Parameters.Add(ProceParam.ParameterName, ProceParam.SqlDbType).Direction = ProceParam.Direction;
                            //赋值
                            SqlCmd.Parameters[ProceParam.ParameterName].Value = Params[ProceParam.ParameterName];
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message + "关键字为：" + ProceParam.ParameterName);
                        }
                    }


                    switch (execType)
                    {
                        case ExecType.Exec:
                            SqlCmd.ExecuteNonQuery();
                            foreach (SqlParameter ProceParam in SqlCmd.Parameters)
                            {
                                if (ProceParam.Direction == ParameterDirection.InputOutput || ProceParam.Direction == ParameterDirection.Output)
                                    Params[ProceParam.ParameterName] = ProceParam.Value;
                            }
                            return null;
                        case ExecType.Open:
                            SqlDataAdapter SDA = new SqlDataAdapter(SqlCmd);
                            DataSet TempDS = new DataSet();
                            SDA.Fill(TempDS);

                            foreach (SqlParameter ProceParam in SqlCmd.Parameters)
                            {
                                if (ProceParam.Direction == ParameterDirection.InputOutput || ProceParam.Direction == ParameterDirection.Output)
                                    Params[ProceParam.ParameterName] = ProceParam.Value;
                            }

                            return TempDS;
                    }
                    return null;

                }
            }
            catch (Exception e)
            {
                throw e;
            }
        }
        #endregion

    }
}
